﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace WINBASE.Utils.DBHelper
{
	public class QueryHelper
	{
		#region Property

		private SqlConnection _Connection = null;
		public SqlConnection Connection
		{
			get { return _Connection; }
			set { _Connection = value; }
		}

		#endregion

		#region Constructor

		public QueryHelper()
		{
			this.Connection = new SqlConnection(WINBASE.Properties.Settings.Default.DefaultConnectionString);
		}

		public QueryHelper(string ConnectionString)
		{
			this.Connection = new SqlConnection(ConnectionString);
		}

		public QueryHelper(SqlConnection Connection)
		{
			this.Connection = Connection;
		}

		#endregion

		#region GetASchemaDataTable

		public DataTable GetASchemaDataTable(string ViewName)
		{
			DataTable dtSchema = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(GetACommand("sp_columns @s", ViewName)))
            {
                da.Fill(dtSchema);
            }
			return dtSchema;
		}

		#endregion

		#region GetAView

		public DataTable GetAView(string ViewName, params string[] SortBy)
		{
			string strSortCol = "";
			foreach (string strCol in SortBy)
			{
				strSortCol += strSortCol == "" ? strCol : ", " + strCol;
			}
			return GetADataTable("SELECT * FROM " + ViewName + (strSortCol.Trim() != "" ? " ORDER BY " : "") + strSortCol);
		}

		public DataTable GetAView(string ViewName, string Filter, params string[] SortBy)
		{
			string strSortCol = "";
			foreach (string strCol in SortBy)
			{
				strSortCol += strSortCol == "" ? strCol : ", " + strCol;
			}
			return GetADataTable("SELECT * FROM " + ViewName + " WHERE " + Filter + " ORDER BY " + strSortCol);
		}

		public DataTable GetAView(string ViewName, int NoOfRows, params string[] SortBy)
		{
			string strSortCol = "";
			foreach (string strCol in SortBy)
			{
				strSortCol += strSortCol == "" ? strCol : ", " + strCol;
			}
			return GetADataTable("SELECT TOP " + NoOfRows.ToString() + " * FROM " + ViewName + " ORDER BY " + strSortCol);
		}

        public DataTable GetAView(string ViewName, int NoOfRows, string Filter, params string[] SortBy)
        {
            string strSortCol = "";
            foreach (string strCol in SortBy)
            {
                strSortCol += strSortCol == "" ? strCol : ", " + strCol;
            }
            return GetADataTable("SELECT TOP " + NoOfRows.ToString() + " * FROM " + ViewName + " WHERE " + Filter + " ORDER BY " + strSortCol);
        }

		#endregion

		#region GetADataTable

		public DataTable GetADataTable(string CommandText)
		{
			return GetADataTable(CommandText, new List<object>() { });
		}

		public DataTable GetADataTable(string CommandText, params object[] Parameters)
		{
			return GetADataTable(CommandText, Parameters.ToList());
		}

		public DataTable GetADataTable(string CommandText, List<object> Parameters)
		{
			DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(GetACommand(CommandText, Parameters)))
            {
                da.Fill(dt);
            }
			return dt;
		}

		#endregion

		#region GetADataRow

		public DataRow GetADataRow(string CommandText)
		{
			return GetADataRow(CommandText, new List<object>() { });
		}

		public DataRow GetADataRow(string CommandText, params object[] Parameters)
		{
			return GetADataRow(CommandText, Parameters.ToList());
		}

		public DataRow GetADataRow(string CommandText, List<object> Parameters)
		{
            DataRow result;
            using (DataTable dt = GetADataTable(CommandText, Parameters))
            {
                if (dt.Rows.Count > 0)
                {
                    result = dt.Rows[0];
                }
                else result = null;
            }
            return result;
		}

		#endregion

		#region GetARow

		public object[] GetARow(string CommandText)
		{
			return getARow(CommandText, new List<object>() { });
		}

		public object[] GetARow(string CommandText, params object[] Parameters)
		{
			return getARow(CommandText, Parameters.ToList());
		}

		public object[] getARow(string CommandText, List<object> Parameters)
		{
			object[] result;
            using (SqlCommand cmd = GetACommand(CommandText, Parameters))
            {
                try
                {
                    Connection.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (!dr.HasRows) result = null;
                    else
                    {
                        result = new object[dr.Depth];
                        int i = 0;
                        while (dr.Read())
                        {
                            result[i] = dr[i];
                            i++;
                        }
                    }
                    dr.Dispose();
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw;
                }
            }
			return result;
		} 
		
		#endregion

		#region GetAVal

		public object GetAVal(string CommandText)
		{
			return GetAVal(CommandText, new List<object>() { });
		}

		public object GetAVal(string CommandText, params object[] Parameters)
		{
			return GetAVal(CommandText, Parameters.ToList());
		}

		public object GetAVal(string CommandText, List<object> Parameters)
		{
			object result;
            using (SqlCommand cmd = GetACommand(CommandText, Parameters))
            {
                try
                {
                    Connection.Open();
                    result = cmd.ExecuteScalar();
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw;
                }
            }
			return result;
		}

		public object GetAValProc(string CommandText)
		{
			return GetAValProc(CommandText, new List<object>() { });
		}

		public object GetAValProc(string CommandText, params object[] Parameters)
		{
			return GetAValProc(CommandText, Parameters.ToList());
		}

		public object GetAValProc(string StoreProcedureName, List<object> Parameters)
		{
			object result;
            using (SqlCommand cmd = GetAProcCommand(StoreProcedureName, Parameters))
            {
                try
                {
                    Connection.Open();
                    result = cmd.ExecuteScalar();
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw;
                }
            }
			return result;
		}

		#endregion

		#region ExecProc

		public object ExecProc(string StoreProcedureName, List<object> Params)
		{
			return ExecProc(StoreProcedureName, Params.ToArray());
		}

		public object ExecProc(string StoreProcedureName, params object[] Params)
		{
            object result = null;
            using (SqlCommand cmd = GetACommand(StoreProcedureName))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd);
                    cmd.Connection.Close();

                    if (cmd.Parameters.Count - 1 != Params.Count())
                        throw new InvalidOperationException();

                    for (int i = 1; i <= cmd.Parameters.Count - 1; i++)
                    {
                        cmd.Parameters[i].Value = Params[i - 1] ?? DBNull.Value;
                    }

                    Connection.Open();
                    result = cmd.ExecuteScalar();
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw;
                }
            }
            return result;
		}

        public object ExecProc(string StoreProcedureName, Dictionary<string, object> Params)
        {
            object result = null;
            using (SqlCommand cmd = GetACommand(StoreProcedureName))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd);
                    cmd.Connection.Close();

                    if (Params.Count < (cmd.Parameters.Count - 1))
                    {
                        string invalidParamName = "";
                        foreach (SqlParameter sqlParam in cmd.Parameters)
                        {
                            if (sqlParam.ParameterName != "@RETURN_VALUE")
                            {
                                if (!Params.ContainsKey(sqlParam.ParameterName))
                                {
                                    invalidParamName += (invalidParamName == "" ? "" : "\n") + sqlParam.ParameterName;
                                }
                            }
                        }
                        InvalidOperationException ex = new InvalidOperationException("Incorrect Paramters.\n" + invalidParamName + " are missing.");
                        ShowMsgException(ex);
                        throw ex;
                    }
                    else
                    {
                        for (int i = 1; i <= cmd.Parameters.Count - 1; i++)
                        {
                            cmd.Parameters[i].Value = Params[cmd.Parameters[i].ParameterName] ?? DBNull.Value;
                        }
                    }

                    Connection.Open();
                    cmd.ExecuteNonQuery();
                    result = cmd.Parameters["@RETURN_VALUE"].Value;
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
                return result;
            }
        }

		#endregion

		#region ExecSql

		public bool ExecSql(string CommandText)
		{
			return ExecSql(CommandText, new List<object> { });
		}

		public bool ExecSql(string CommandText, params object[] Parameters)
		{
			return ExecSql(CommandText, Parameters.ToList());
		}

		public bool ExecSql(string CommandText, List<object> Parameters)
		{
            using (SqlCommand cmd = GetACommand(CommandText, Parameters))
            {
                try
                {
                    Connection.Open();
                    cmd.ExecuteNonQuery();
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
            }
			return true;
		}

		#endregion

		#region GetACommand

		public SqlCommand GetACommand(string CommandText)
		{
			return GetACommand(CommandText, new List<object>() { });
		}

		public SqlCommand GetACommand(string CommandText, params object[] Parameters)
		{
            List<object> ParameterList = Parameters.ToList();
            using (SqlCommand cmd = this.Connection.CreateCommand())
            {
                try
                {
                    if (Regex.IsMatch(CommandText, "@s"))
                    {
                        dynamic mcParams = Regex.Matches(CommandText, "@s");
                        if (mcParams.Count != ParameterList.Count)
                            throw new InvalidOperationException();

                        for (int i = 0; i <= ParameterList.Count - 1; i++)
                        {
                            Match mcParam = Regex.Match(CommandText, "@s");
                            CommandText = CommandText.Remove(mcParam.Index, 2).Insert(mcParam.Index, "@Param" + i.ToString());
                            cmd.Parameters.AddWithValue("@Param" + i.ToString(), Parameters[i] ?? DBNull.Value);
                        }
                    }
                    else
                    {
                        // Implement Code for Auto Parameter Mapping
                    }

                    cmd.CommandText = CommandText;
                    return cmd;
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
            }
		}

		public SqlCommand GetACommand(string CommandText, List<object> Parameters)
		{
            using (SqlCommand cmd = this.Connection.CreateCommand())
            {
                try
                {
                    if (Regex.IsMatch(CommandText, "@s"))
                    {
                        dynamic mcParams = Regex.Matches(CommandText, "@s");
                        if (mcParams.Count != Parameters.Count)
                            throw new InvalidOperationException();

                        for (int i = 0; i <= Parameters.Count - 1; i++)
                        {
                            Match mcParam = Regex.Match(CommandText, "@s");
                            CommandText = CommandText.Remove(mcParam.Index, 2).Insert(mcParam.Index, "@Param" + i.ToString());
                            cmd.Parameters.AddWithValue("@Param" + i.ToString(), Parameters[i] ?? DBNull.Value);
                        }
                    }
                    cmd.CommandText = CommandText;
                    return cmd;
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
            }
		}

        public SqlCommand GetACommand(string CommandText, Dictionary<string,object> Parameters)
        {
            using (SqlCommand cmd = this.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd);
                    cmd.Connection.Close();

                    if (cmd.Parameters.Count - 1 != Parameters.Count)
                        throw new InvalidOperationException();

                    foreach (KeyValuePair<string, object> param in Parameters)
                    {
                        cmd.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                    }

                    cmd.CommandText = CommandText;
                    return cmd;
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
            }
        }

		public SqlCommand GetAProcCommand(string CommandText)
		{
			return GetAProcCommand(CommandText, new List<object>() { });
		}

		public SqlCommand GetAProcCommand(string CommandText, params object[] Parameters)
		{
			return GetAProcCommand(CommandText, Parameters.ToList());
		}

		public SqlCommand GetAProcCommand(string StoreProcedureName, List<object> Parameters)
		{
            using (SqlCommand cmd = GetACommand(StoreProcedureName))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(cmd);
                    cmd.Connection.Close();

                    if (cmd.Parameters.Count - 1 != Parameters.Count)
                        throw new InvalidOperationException();

                    for (int i = 1; i <= cmd.Parameters.Count - 1; i++)
                    {
                        cmd.Parameters[i].Value = Parameters[i - 1] ?? DBNull.Value;
                    }
                    return cmd;
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    ShowMsgException(ex);
                    throw ex;
                }
            }
		}

		#endregion

		#region IsValidConnection

		public static bool IsValidConnection(string ConnectionString)
		{
			return IsValidConnection(new SqlConnection(ConnectionString));
		}

		public static bool IsValidConnection(SqlConnection connection)
		{
			try
			{
				bool result = false;
				connection.Open();
				result = true;
				connection.Close();
				return result;
			}
			catch (Exception ex)
			{
				ShowMsgException(ex);
				return false;
			}
		}

		public static bool IsValidConnection(string ConnectionString, ref SqlConnection outSqlConnection)
		{
			SqlConnection mySqlCon = new SqlConnection(ConnectionString);
			if (IsValidConnection(mySqlCon))
			{
				outSqlConnection = mySqlCon;
				return true;
			}
			else return false;
		} 

		#endregion

		public static void ShowMsgException(Exception ex)
		{
			MessageBox.Show(ex.Message + (ex.StackTrace == null ? "" : "\n\nStackTrace\n") + ex.StackTrace, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
			return;
		}
	}

	public class DBSchema
	{
		QueryHelper dbHandler;

		public DBSchema(string ConnectionString)
		{
			dbHandler = new QueryHelper(ConnectionString);
		}

		public string DatabaseName
		{
			get
			{
				return dbHandler.Connection.Database;
			}
		}
		
		public ColumnCollection getColDef(string ObjectName)
		{
			Server myServer = new Server(new ServerConnection(dbHandler.Connection));
			Table myTable = myServer.Databases[dbHandler.Connection.Database].Tables[ObjectName];
			return myTable.Columns;
		}
	}

	public class SPHandler
	{
		private string[] ExcludeColumns = new string[] {"TrxNo", "CreateDateTime", "UpdateDateTime" };

		string connectionstring = "";
		public SPHandler(string ConnectionString)
		{
			connectionstring = ConnectionString;
		}

		public string getStdViewQuery(string TableName,QueryType QType)
		{
			DBSchema myDbSchema = new DBSchema(connectionstring);
			string query = "";

			query += QType.ToString() + " VIEW vw_" + TableName + 
										"\nAS\n" + 
										"SELECT ";
										
			ColumnCollection myColumns = myDbSchema.getColDef(TableName);
			int i = 0;
			foreach (Column myCol in myColumns)
			{
				i++;
				query += "\n\t" + myCol.Name + " AS " + '[' + Regex.Replace(myCol.Name, "([a-z])([A-Z])", "$1 $2") + ']' + (i == myColumns.Count ? "" : ",");
			}

			query += "\nFROM " + TableName;

			return query;
		}

		public string getUserDefinedView(string TableName,QueryType QType)
		{
			string query = "";
			
			return query;
		}

		public string getXMLSchemaQuery()
		{
			return "";
		}

		public string getStdProcQuery(string TableName,QueryType QType, ProcType PType)
		{
			string query = "";

			switch (PType)
			{
				case ProcType.INSERT: query = getInsertSQL(TableName, QType); break;
				case ProcType.UPDATE: query = getUpdateSQL(TableName, QType); break;
				case ProcType.DELETE: query = getDeleteSQL(TableName, QType); break;
			}

			query += "\n\nIF @@Error <> 0\n\tRETURN -1\nELSE\n\tRETURN 0";
			return query;
		}


		protected internal string getDeleteSQL(string TableName, QueryType QType)
		{
			string SQL = "";
			string strParam1 = "";
			string strCondition = "";

			DBSchema myDbSchema = new DBSchema(connectionstring);
			ColumnCollection myColumns = myDbSchema.getColDef(TableName);
			foreach (Column myCol in myColumns)
			{
				if (!ExcludeColumns.Contains(myCol.Name))
				{
					if (myCol.InPrimaryKey)
					{
						strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
						strCondition += getCondition(myCol);
					}
				}
			}
			strParam1 += ",\n\t@UpdateBy " + myColumns["UpdateBy"].DataType.SqlDataType + "[" + myColumns["UpdateBy"].DataType.MaximumLength.ToString() + "],\n\t" +
								"@Type Int = Null";
			SQL += QType.ToString() + " PROCEDURE spd_" + TableName +
						strParam1 + "\nAS\n\n" +
					 "UPDATE " + TableName +
					 "\nSET StatusCode = CASE WHEN @Type = 1 THEN 'USE' ELSE 'DEL' END," +
						"\n\tUpdateBy=@UpdateBy," +
						"\n\tUpdateDateTime=GetDate()" +
					 "\nWHERE " + strCondition;

			return SQL;
		}

		protected internal string getUpdateSQL(string TableName, QueryType QType)
		{
			string SQL = "";
			string strParam1 = "";
			string strDefault = "";
			string strColumns = "";
			string strCondition = "";

			DBSchema myDbSchema = new DBSchema(connectionstring);
			ColumnCollection myColumns = myDbSchema.getColDef(TableName);
			foreach (Column myCol in myColumns)
			{
				if (!ExcludeColumns.Contains(myCol.Name))
				{
					strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
					strDefault += getParamDefaultString(myCol);
					strColumns += (strColumns == "" ? "" : ",\n\t") + myCol.Name + "=@" + myCol.Name;
					if (myCol.InPrimaryKey) strCondition += getCondition(myCol);
				}
			}
			SQL += QType.ToString() + " PROCEDURE spu_" + TableName +
						strParam1 + "\nAS\n" +
					 strDefault + "\n\n" +
					 "UPDATE " + TableName + "\n" +
					 "SET " + strColumns + "\n" +
					 "WHERE " + strCondition;

			return SQL;
		}

		protected internal string getInsertSQL(string TableName, QueryType QType)
		{
			string SQL = "";
			string strParam1 = "";
			string strParam2 = "";
			string strColumns = "";
			string strDefault = "";

			DBSchema myDbSchema = new DBSchema(connectionstring);
			ColumnCollection myColumns = myDbSchema.getColDef(TableName);
			foreach (Column myCol in myColumns)
			{
				if (!ExcludeColumns.Contains(myCol.Name))
				{
					strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
					strDefault += getParamDefaultString(myCol);
					strColumns += (strColumns == "" ? "" : ",\n\t") + myCol.Name;
					strParam2 += (strParam2 == "" ? "@" : ",\n\t@") + myCol.Name;
				}
			}
			SQL += QType.ToString() + " PROCEDURE spi_" + TableName +
							strParam1 + "\nAS\n" +
					 strDefault +
					 "\n\nINSERT INTO " + TableName + "\n\t" +
					 "(" + strColumns + ")" + "\n" +
					 "VALUES (" + strParam2 + ")";

			return SQL;
		}

		protected internal string getCondition(Column col)
		{
			string result = "";
			result += (result == "" ? "" : " AND\n\t") + col.Name + "=@" + col.Name;
			return result;
		}

		protected internal string getParamString(Column col)
		{
			string result = "";
			string myColName = col.Name;
			DataType myDataType = col.DataType;

			if (myDataType.SqlDataType == SqlDataType.DateTime ||
				myDataType.SqlDataType == SqlDataType.Xml ||
				myDataType.SqlDataType == SqlDataType.Image ||
				myDataType.SqlDataType == SqlDataType.Int ||
				myDataType.SqlDataType == SqlDataType.NText)
			{
				result += "\n\t@" + myColName + " " + myDataType.SqlDataType.ToString();
			}
			else
			{
				result += "\n\t@" + myColName + " " + myDataType.SqlDataType.ToString() + "(" + myDataType.MaximumLength + ")";
			}

			return result;
		}

		protected internal string getParamDefaultString(Column col)
		{
			string result = "";

			switch (col.DataType.SqlDataType)
			{
				case SqlDataType.Char:
				case SqlDataType.VarChar:
				case SqlDataType.NChar:
				case SqlDataType.NVarChar:
				case SqlDataType.NText:
				case SqlDataType.Text:
					result += "\nIF LEN(@" + col.Name + ") = 0" + " SELECT @" + col.Name + " = NULL";
					break;
				case SqlDataType.Date:
				case SqlDataType.DateTime:
					result += "\nIf @" + col.Name + " = {d '1899-12-31'} Select @" + col.Name + " = Null";
					break;
				default:
					result += "\nIF LEN(@" + col.Name + ") = 0";
					break;
			}

			return result;
		}
	}
}